import Router from "koa-router";
import { authMiddleware } from "../../../middleware/auth";
import { pool } from "../../../config/database";
import { RowDataPacket, ResultSetHeader } from "mysql2";
import { 
  checkAdminPermission, 
  getPaginationParams, 
  createPaginatedResponse, 
  getSearchParams,
  handleError,
  successResponse,
  validateId,
  validateBatchIds
} from "../common";

const router = new Router({ prefix: "/api/admin/categories" });

// 获取类别列表 - 支持搜索和分页
router.get("/", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { page, limit, offset } = getPaginationParams(ctx);
    const { keyword, parent_id, is_active } = getSearchParams(ctx);

    // 构建查询条件
    let whereClause = "WHERE 1=1";
    const params: any[] = [];

    if (keyword) {
      whereClause += " AND (name LIKE ? OR description LIKE ? OR slug LIKE ?)";
      const searchTerm = `%${keyword}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

    if (parent_id !== undefined) {
      if (parent_id === 'null' || parent_id === null) {
        whereClause += " AND parent_id IS NULL";
      } else {
        whereClause += " AND parent_id = ?";
        params.push(parseInt(parent_id as string));
      }
    }

    if (is_active !== undefined) {
      whereClause += " AND is_active = ?";
      params.push(is_active === 'true' ? 1 : 0);
    }

    // 获取总数
    const [countResult] = await pool.execute(
      `SELECT COUNT(*) as total FROM categories ${whereClause}`,
      params
    ) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取类别列表
    const [categories] = await pool.execute(`
      SELECT 
        c.*, 
        p.name as parent_name,
        (SELECT COUNT(*) FROM categories WHERE parent_id = c.id) as children_count,
        (SELECT COUNT(*) FROM products WHERE category_id = c.id) as products_count
      FROM categories c
      LEFT JOIN categories p ON c.parent_id = p.id
      ${whereClause}
      ORDER BY c.sort_order ASC, c.created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `, params) as [RowDataPacket[], any];

    const response = createPaginatedResponse(categories, total, page, limit);
    successResponse(ctx, response, "获取类别列表成功");

  } catch (error) {
    handleError(ctx, error, "获取类别列表失败");
  }
});

// 获取类别树形结构
router.get("/tree", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { is_active } = ctx.query;
    
    let whereClause = "WHERE 1=1";
    const params: any[] = [];
    
    if (is_active !== undefined) {
      whereClause += " AND is_active = ?";
      params.push(is_active === 'true' ? 1 : 0);
    }

    // 获取所有类别
    const [categories] = await pool.execute(`
      SELECT 
        id, name, slug, description, parent_id, image, icon, 
        sort_order, is_active, meta_title, meta_description,
        created_at, updated_at
      FROM categories
      ${whereClause}
      ORDER BY sort_order ASC, name ASC
    `, params) as [RowDataPacket[], any];

    // 构建树形结构
    const buildTree = (items: any[], parentId: number | null = null): any[] => {
      return items
        .filter(item => item.parent_id === parentId)
        .map(item => ({
          ...item,
          children: buildTree(items, item.id)
        }));
    };

    const tree = buildTree(categories);
    successResponse(ctx, tree, "获取类别树形结构成功");

  } catch (error) {
    handleError(ctx, error, "获取类别树形结构失败");
  }
});

// 获取类别详情
router.get("/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const id = validateId(ctx);
    if (id === null) return;

    const [categories] = await pool.execute(`
      SELECT 
        c.*, 
        p.name as parent_name,
        (SELECT COUNT(*) FROM categories WHERE parent_id = c.id) as children_count,
        (SELECT COUNT(*) FROM products WHERE category_id = c.id) as products_count
      FROM categories c
      LEFT JOIN categories p ON c.parent_id = p.id
      WHERE c.id = ?
    `, [id]) as [RowDataPacket[], any];

    if (categories.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "类别不存在" };
      return;
    }

    successResponse(ctx, categories[0], "获取类别详情成功");

  } catch (error) {
    handleError(ctx, error, "获取类别详情失败");
  }
});

// 创建类别
router.post("/", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { 
      name, slug, description, parent_id, image, icon,
      sort_order = 0, is_active = true, meta_title, meta_description
    } = ctx.request.body as {
      name: string;
      slug: string;
      description?: string;
      parent_id?: number;
      image?: string;
      icon?: string;
      sort_order?: number;
      is_active?: boolean;
      meta_title?: string;
      meta_description?: string;
    };

    // 参数验证
    if (!name || !slug) {
      ctx.status = 400;
      ctx.body = { success: false, message: "类别名称和别名为必填项" };
      return;
    }

    // 检查slug唯一性
    const [existingSlugs] = await pool.execute(
      "SELECT id FROM categories WHERE slug = ?",
      [slug]
    ) as [RowDataPacket[], any];

    if (existingSlugs.length > 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "类别别名已存在" };
      return;
    }

    // 如果有parent_id，检查父类别是否存在
    if (parent_id) {
      const [parentCategories] = await pool.execute(
        "SELECT id FROM categories WHERE id = ?",
        [parent_id]
      ) as [RowDataPacket[], any];

      if (parentCategories.length === 0) {
        ctx.status = 400;
        ctx.body = { success: false, message: "父类别不存在" };
        return;
      }
    }

    // 创建类别
    const [result] = await pool.execute(
      `INSERT INTO categories 
       (name, slug, description, parent_id, image, icon, sort_order, is_active, meta_title, meta_description) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        name, slug, description || null, parent_id || null, image || null, 
        icon || null, sort_order, is_active ? 1 : 0, meta_title || null, meta_description || null
      ]
    ) as [ResultSetHeader, any];

    const [newCategory] = await pool.execute(
      "SELECT * FROM categories WHERE id = ?",
      [result.insertId]
    ) as [RowDataPacket[], any];

    successResponse(ctx, newCategory[0], "类别创建成功");

  } catch (error) {
    handleError(ctx, error, "创建类别失败");
  }
});

// 更新类别
router.put("/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const id = validateId(ctx);
    if (id === null) return;

    const updateData = ctx.request.body as {
      name?: string;
      slug?: string;
      description?: string;
      parent_id?: number;
      image?: string;
      icon?: string;
      sort_order?: number;
      is_active?: boolean;
      meta_title?: string;
      meta_description?: string;
    };

    // 检查类别是否存在
    const [categories] = await pool.execute(
      "SELECT id FROM categories WHERE id = ?",
      [id]
    ) as [RowDataPacket[], any];

    if (categories.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "类别不存在" };
      return;
    }

    // 检查slug唯一性
    if (updateData.slug) {
      const [existingSlugs] = await pool.execute(
        "SELECT id FROM categories WHERE slug = ? AND id != ?",
        [updateData.slug, id]
      ) as [RowDataPacket[], any];

      if (existingSlugs.length > 0) {
        ctx.status = 400;
        ctx.body = { success: false, message: "类别别名已存在" };
        return;
      }
    }

    // 如果更新parent_id，检查不能设置为自己或自己的子类别
    if (updateData.parent_id !== undefined && updateData.parent_id !== null) {
      if (updateData.parent_id === id) {
        ctx.status = 400;
        ctx.body = { success: false, message: "不能将类别设置为自己的父类别" };
        return;
      }

      // 检查是否会形成循环引用
      const checkCircular = async (parentId: number, targetId: number): Promise<boolean> => {
        if (parentId === targetId) return true;
        
        const [parent] = await pool.execute(
          "SELECT parent_id FROM categories WHERE id = ?",
          [parentId]
        ) as [RowDataPacket[], any];
        
        if (parent.length > 0 && parent[0].parent_id) {
          return await checkCircular(parent[0].parent_id, targetId);
        }
        
        return false;
      };

      const hasCircular = await checkCircular(updateData.parent_id, id);
      if (hasCircular) {
        ctx.status = 400;
        ctx.body = { success: false, message: "不能设置子类别为父类别，会形成循环引用" };
        return;
      }
    }

    // 构建更新语句
    const updateFields: string[] = [];
    const updateValues: any[] = [];

    Object.keys(updateData).forEach(key => {
      const value = updateData[key as keyof typeof updateData];
      if (value !== undefined) {
        if (key === 'is_active') {
          updateFields.push(`${key} = ?`);
          updateValues.push(value ? 1 : 0);
        } else {
          updateFields.push(`${key} = ?`);
          updateValues.push(value);
        }
      }
    });

    if (updateFields.length === 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "没有提供要更新的字段" };
      return;
    }

    updateFields.push("updated_at = NOW()");
    updateValues.push(id);

    // 执行更新
    await pool.execute(
      `UPDATE categories SET ${updateFields.join(", ")} WHERE id = ?`,
      updateValues
    );

    const [updatedCategory] = await pool.execute(
      "SELECT * FROM categories WHERE id = ?",
      [id]
    ) as [RowDataPacket[], any];

    successResponse(ctx, updatedCategory[0], "类别更新成功");

  } catch (error) {
    handleError(ctx, error, "更新类别失败");
  }
});

// 删除类别
router.delete("/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const id = validateId(ctx);
    if (id === null) return;

    // 检查类别是否存在
    const [categories] = await pool.execute(
      "SELECT id FROM categories WHERE id = ?",
      [id]
    ) as [RowDataPacket[], any];

    if (categories.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "类别不存在" };
      return;
    }

    // 检查是否有子类别
    const [children] = await pool.execute(
      "SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
      [id]
    ) as [RowDataPacket[], any];

    if (children[0].count > 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "该类别下还有子类别，请先删除子类别" };
      return;
    }

    // 检查是否有商品使用此类别
    const [products] = await pool.execute(
      "SELECT COUNT(*) as count FROM products WHERE category_id = ?",
      [id]
    ) as [RowDataPacket[], any];

    if (products[0].count > 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "该类别下还有商品，请先将商品移动到其他类别" };
      return;
    }

    // 删除类别
    await pool.execute("DELETE FROM categories WHERE id = ?", [id]);

    successResponse(ctx, { id }, "类别删除成功");

  } catch (error) {
    handleError(ctx, error, "删除类别失败");
  }
});

// 批量更新类别状态
router.post("/batch-update-status", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const ids = validateBatchIds(ctx);
    if (ids === null) return;

    const { is_active } = ctx.request.body as { is_active: boolean };

    if (is_active === undefined) {
      ctx.status = 400;
      ctx.body = { success: false, message: "状态不能为空" };
      return;
    }

    const [result] = await pool.execute(
      `UPDATE categories SET is_active = ?, updated_at = NOW() WHERE id IN (${ids.map(() => '?').join(',')})`,
      [is_active ? 1 : 0, ...ids]
    ) as [ResultSetHeader, any];

    successResponse(ctx, { 
      updatedCount: result.affectedRows,
      is_active 
    }, `批量更新状态成功，影响${result.affectedRows}个类别`);

  } catch (error) {
    handleError(ctx, error, "批量更新状态失败");
  }
});

// 获取类别统计信息
router.get("/statistics/overview", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    // 类别统计
    const [categoryStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total_categories,
        COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_categories,
        COUNT(CASE WHEN is_active = 0 THEN 1 END) as inactive_categories,
        COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as root_categories,
        COUNT(CASE WHEN parent_id IS NOT NULL THEN 1 END) as child_categories
      FROM categories
    `) as [RowDataPacket[], any];

    // 各类别的商品数量
    const [productCounts] = await pool.execute(`
      SELECT 
        c.id, c.name, c.slug,
        COUNT(p.id) as products_count
      FROM categories c
      LEFT JOIN products p ON c.id = p.category_id
      WHERE c.is_active = 1
      GROUP BY c.id, c.name, c.slug
      ORDER BY products_count DESC
      LIMIT 10
    `) as [RowDataPacket[], any];

    successResponse(ctx, {
      overview: categoryStats[0],
      topCategories: productCounts
    }, "获取类别统计成功");

  } catch (error) {
    handleError(ctx, error, "获取类别统计失败");
  }
});

export default router; 